﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class YCAFSSTYLEData : BaseFunction
    {
        
        public YCAFSSTYLEData()
            : base()
        {

        }
        public YCAFSSTYLEData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetFSSTAMM(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "FSSTAMM";
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            string ARTTYPE = dwABPOS["ARTTYPE"].ToString().Trim();
            string COLL = dwABPOS["COLL"].ToString().Trim();
            string REASON = dwABPOS["REASON"].ToString().Trim();
            string PRDGRP = dwABPOS["PRDGRP"].ToString().Trim();
            string ARTGRP = dwABPOS["ARTGRP"].ToString().Trim();
            string MATERIAL = dwABPOS["MATERIAL"].ToString().Trim();
            string STANDARD = dwABPOS["STANDARD"].ToString().Trim();
            string SAFTY = dwABPOS["SAFTY"].ToString().Trim();
            string PURCHASER = dwABPOS["PURCHASER"].ToString().Trim();
            string SIZE = dwABPOS["SIZE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct a.fsart as style,a.fsabez as stylename, "
                    + " agg(distinct trim(d.fsffarbe)||'-'||trim(d.fsffbez)) over(partition by a.fsart) as colorname, "
                    + " agg(distinct trim(e.fsggroe)||'-'||trim(f.fsegrbez)) over(partition by a.fsart) as sizename, "
                    + " c.fswprak*0.01 as salesprice,c.fswprku*0.01 as costprice,a.fsprogrp as type, "
                    + " (select trim(substr(sddaten,2,40)) from sddat where sdsa = '0196'  "
                    + " and substr(sdkey,24,1)='V' and trim(substr(sdkey,3,15))=trim(a.fsprogrp)) as typename, "
                    + " a.fskoll as coll,a.fsartprgr as reason, "
                    + " (select trim(substr(sddaten,2,40)) from sddat where sdsa = '0247'  "
                    + " and substr(sdkey,24,1)='V' and trim(substr(sdkey,3,15))=trim(a.fsartprgr)) as reasonname, "
                    + " a.fsartgrp AS prdgroup, "
                    + " (select trim(substr(sddaten,2,20)) from sddat where sdsa = '0178'  "
                    + " and substr(sdkey,24,1)='V' and trim(substr(sdkey,3,15))=trim(a.fsartgrp)) as prdgrpname, "
                    + " a.fsmatart as artgroup, "
                    + " (select trim(substr(sddaten,2,40)) from sddat where sdsa = '0649'  "
                    + " and substr(sdkey,24,1)='V' and trim(substr(sdkey,3,15))=trim(a.fsmatart)) as artgrpname, "
                    + " a.fsmatgrp as material, "
                    + " (select trim(substr(sddaten,2,40)) from sddat where sdsa = '0312' "
                    + " and trim(substr(sdkey,3,15))=trim(a.fsmatgrp)) as materialname, "
                    + " a.fsprodh as countrystandard,b.des as saftyname, "
                    + " a.fsuebm as unit,a.fsanab contdate,a.fsalbis as markdate, "
                    + " a.fssachb as purchaser,A.FSGRRAS "
                    + " from fsstamm a,(select substr(sdkey,3,9) as code,trim(substr(sddaten,2,20)) as des "
                    + " from sddat where sdsa = '0683') b,fswert c,fsfarben d,fsgroe e,fsegroe f "
                    + " where a.fsmatkl = b.code "
                    + " and a.fsmandant = c.fswmandant(+) and a.fsart = c.fswart(+) and a.fsvar = c.fswvar(+) "
                    + " and a.fsmandant = d.fsfmandant and a.fsart = d.fsfart and a.fsvar = d.fsfvar "
                    +" and a.fsmandant = e.fsgmandant and a.fsart = e.fsgart and a.fsvar = e.fsgvar "
                    + " and e.fsgmandant = f.fsemandant and e.fsggroe = f.fsegroes "
                    +" and a.fsart like '2%' and a.fsstakz ='A' and d.fsfstakz = 'A' "
                    + " and a.fsart like '" + ARTICLE + "%'  "
                    + " AND (TRIM(a.fsprogrp) = '" + ARTTYPE + "' OR '" + ARTTYPE + "' = 'XXXXXX') "
                    + " AND (TRIM(a.fskoll) = '" + COLL + "' OR '" + COLL + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsartprgr) = '" + REASON + "' OR '" + REASON + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsartgrp) = '" + PRDGRP + "' OR '" + PRDGRP + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsmatart) = '" + ARTGRP + "' OR '" + ARTGRP + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsmatgrp) = '" + MATERIAL + "' OR '" + MATERIAL + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsprodh) = '" + STANDARD + "' OR '" + STANDARD + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fsmatkl) = '" + SAFTY + "' OR '" + SAFTY + "'  = 'XXXXXX') "
                    + " AND (TRIM(a.fssachb) = '" + PURCHASER + "' OR '" + PURCHASER + "'  = 'XXXXXX') "
                    + " AND (TRIM(A.FSGRRAS) = '" + SIZE + "' OR '" + SIZE + "'  = 'XXXXXX') "
                    + " order by 1 ";
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["STYLENAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["STYLENAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["COLORNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["COLORNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["TYPENAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["TYPENAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REASONNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REASONNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["PRDGRPNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["PRDGRPNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTGRPNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTGRPNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["MATERIALNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["MATERIALNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["saftyname"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["saftyname"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["purchaser"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["purchaser"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet BuildYCAFSSTYLEData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetFSSTAMM(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}